﻿<?xml version="1.0" encoding="utf-8" ?>
<root>
	<element name="Identity Insert">
		--setting identity-insert off allows you to insert values into identity columns. 
		--if you don't specify a table name, it will set it for the Database that you're in.
			  
		set identity_insert Person on
		insert into Person(IdentityColumn, FirstName) values (1, 'Colin')
		insert into Person(IdentityColumn, FirstName) values (2, 'is')
		insert into Person(IdentityColumn, FirstName) values (3, 'an')
		insert into Person(IdentityColumn, FirstName) values (4, 'ok')
		insert into Person(IdentityColumn, FirstName) values (5, 'guy')
		set identity_insert Person off
	</element>
	<element name="Set No Count Off">
		--Normally sql statements will output the number of rows affected... don't need that in stored procedures.

		SET NOCOUNT ON
	</element>
	<element name="Scope Identity">
		--getting the inserted identity column. this is better than just getting the @@identity, since it is limited
		--to the current scope

		select @param = SCOPE_IDENTITY()
	</element>
	<element name="Role / User - Creation / Assigning">
		--Create a role:
		EXEC dbo.sp_addrole @rolename = N'RoleName', @ownername = N'dbo'

		--Create a user:
		EXEC master.dbo.sp_grantlogin @loginame = N'VeryCoolDomain\UltraCoolUserName'

		--Grant User access to the database:
		EXEC dbo.sp_grantdbaccess @loginame = N'VeryCoolDomain\UltraCoolUserName', @name_in_db = N'UltraCoolUserName'

		--Grant User access to the role
		EXEC dbo.sp_addrolemember @rolename =N'RoleName', @membername = N'UltraCoolUserName'

		--Create a sql server user:
		EXEC master.dbo.sp_addlogin @loginame = N'UltraCoolUserName', @passwd = 'passsssss', @defdb = N'master', @deflanguage = N'us_english'
	</element>
	<element name="Coalesce">
		--The Coalesce function returns the first non-null parameter. So, in the below case, if you passed in a null
		--parameter for @FirstName, it would return every record. Now, if the FirstName column is null, then
		--it will be omitted, because "null=null" returns false. so you could do the second example if that column
		--was nullable

		--Non-nullable firstname column
		select FirstName,LastName,
		from People
		where FirstName = coalesce(@FirstName,FirstName)

		--Nullable firstname column
		select FirstName,LastName,
		from People
		where coalesce(FirstName,'') = coalesce(@FirstName,FirstName,'')
	</element>
	<element name="Select Columns">
		--this is set up for sql server 2000. i'm betting it's much easier if you assume you're on sql server 2005+.
		--certain columns have duplicate rows called 'sysname'. we ignore those.
		--nchar and nvarchar use twice as much room for the character encoding, so we have to half it in the display.
		
		select
			o.name as TableName,
			c.name as ColumnName,
			COLUMNPROPERTY(o.id,c.name,'IsIdentity' ) isIdentity,
			case when p.Column_Name is not null then 1 else 0 end as IsPrimaryKey,
			c.colorder as ColumnOrder,
			CASE
				WHEN t.name IN ('char', 'varchar') THEN
				( CAST(t.name AS [varchar]) + ' (' + CAST(c.length AS [varchar]) + ')' )
				WHEN t.name IN ('nchar', 'nvarchar') THEN
				( CAST(t.name AS [varchar]) + ' (' + CAST(c.length/2 AS [varchar]) + ')' )
				WHEN t.name IN ('numeric', 'decimal') THEN
				( CAST(t.name AS [varchar]) + ' (' + CAST(c.xprec AS [varchar]) + ',' + CAST(c.xscale AS [varchar]) + ')' )
				ELSE t.name
			END AS DataType,
			c.IsNullable
		from
			syscolumns c
			inner join sysobjects o
				on c.id=o.id
			INNER JOIN systypes t
				ON c.xtype = t.xtype
			left join (
				SELECT c.Table_Name, k.Column_Name
				FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
				inner JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k ON k.table_name = c.table_name AND k.table_schema = c.table_schema AND k.table_catalog = c.table_catalog AND k.constraint_catalog = c.constraint_catalog AND k.constraint_name = c.constraint_name
				where constraint_type = 'PRIMARY KEY'
			) p
				on o.Name = p.Table_Name and c.Name = p.Column_Name
		where
			o.xtype='U' and
			o.name &lt;&gt; 'dtproperties'
			and t.name &lt;&gt; 'sysname'
			and o.name like '%'
			order by o.name,c.colorder
	</element>
	<element name="Select Tables">
		select o.name as TableName
		from sysobjects o where o.xtype='U' and o.name &lt;&gt; 'dtproperties'
		order by o.name
	</element>
	<element name="Select Databases">
		SELECT name FROM sysdatabases where name not in ('master','model','msdb','tempdb') order by name
	</element>
	<element name="Select Procedures">
		--assumes you're not naming your procedures in such a way as to be confused with the system stored procedures.

		SELECT specific_Name, routine_type, created, last_altered, routine_definition
		FROM INFORMATION_SCHEMA.ROUTINES
		where substring(specific_Name,1,3) not in ('dt_','sp_')
		and routine_type = 'PROCEDURE'
	</element>
	<element name="Select Functions">
		--assumes you're not naming your procedures in such a way as to be confused with the system stored procedures.

		SELECT specific_Name, routine_type, created, last_altered, routine_definition
		FROM INFORMATION_SCHEMA.ROUTINES
		where substring(specific_Name,1,3) not in ('dt_','sp_')
		and routine_type = 'FUNCTION'
	</element>
	<element name="Get Usage of View">
		select * from INFORMATION_SCHEMA.VIEW_TABLE_USAGE
		select * from INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
	</element>
	<element name="Select Indexes (not perfect)">
		declare @empty varchar(1)
		select @empty = ''

		declare @IgnoreDuplicateKeys varchar(35), @Unique varchar(35), @IgnoreDuplicateRows varchar(35), @Clustered varchar(35), @Hypotethical varchar(35), @Statistics varchar(35), @PrimaryKey varchar(35), @UniqueKey varchar(35), @AutoCreate varchar(35), @StatsNoRecompute varchar(35)

		select [type], number, [name] into #tempSptValues from master.dbo.spt_values

		select @IgnoreDuplicateKeys = name from #tempSptValues where type = 'I' and number = 1
		select @Unique = name from #tempSptValues where type = 'I' and number = 2
		select @IgnoreDuplicateRows = name from #tempSptValues where type = 'I' and number = 4
		select @Clustered = name from #tempSptValues where type = 'I' and number = 16
		select @Hypotethical = name from #tempSptValues where type = 'I' and number = 32
		select @Statistics = name from #tempSptValues where type = 'I' and number = 64
		select @PrimaryKey = name from #tempSptValues where type = 'I' and number = 2048
		select @UniqueKey = name from #tempSptValues where type = 'I' and number = 4096
		select @AutoCreate = name from #tempSptValues where type = 'I' and number = 8388608
		select @StatsNoRecompute = name from #tempSptValues where type = 'I' and number = 16777216

		drop table #tempSptValues

		select
			o.name as tableViewName,
			i.name as columnName,
			[index description] =
				convert(varchar(210), --bits 16 off, 1, 2, 16777216 on
					case when (i.status &amp; 16)&lt;&gt;0 then @Clustered else 'non'+@Clustered end
					+ case when (i.status &amp; 1)&lt;&gt;0 then ', '+@IgnoreDuplicateKeys else @empty end
					+ case when (i.status &amp; 2)&lt;&gt;0 then ', '+@Unique else @empty end
					+ case when (i.status &amp; 4)&lt;&gt;0 then ', '+@IgnoreDuplicateRows else @empty end
					+ case when (i.status &amp; 64)&lt;&gt;0 then ', '+@Statistics else
					case when (i.status &amp; 32)&lt;&gt;0 then ', '+@Hypotethical else @empty end end
					+ case when (i.status &amp; 2048)&lt;&gt;0 then ', '+@PrimaryKey else @empty end
					+ case when (i.status &amp; 4096)&lt;&gt;0 then ', '+@UniqueKey else @empty end
					+ case when (i.status &amp; 8388608)&lt;&gt;0 then ', '+@AutoCreate else @empty end
					+ case when (i.status &amp; 16777216)&lt;&gt;0 then ', '+@StatsNoRecompute else @empty end
				),
			[index column 1] = index_col(o.name,indid, 1),
			[index column 2] = index_col(o.name,indid, 2),
			[index column 3] = index_col(o.name,indid, 3)
		from
			dbo.sysindexes i
			inner join dbo.sysobjects o
				on i.id = o.id
		where indid &gt; 0 and indid &lt; 255 --all the clustered (=1), non clusterd (&gt;1 and &lt;251), and text or image (=255)
		and o.type in ('V','U') --user table / view
		--ignore the indexes for the autostat
		and (i.status &amp; 64) = 0 --index with duplicates
		and (i.status &amp; 8388608) = 0 --auto created index
		and (i.status &amp; 16777216)= 0 --stats no recompute
		order by o.name
	</element>
</root>
